/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package queenb.web.organization.controller;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.*;
import javax.faces.model.SelectItem;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import queenb.net.entitybean.*;
import queenb.web.controller.QueenbController;
import queenb.web.crm.store.N900Store;
import queenb.web.organization.store.*;
import queenb.web.store.N500Store;
import queenb.web.util.ResourcesProvider;

/**
 *
 * @author Nguyen Hung
 */
public class OrganizationController extends QueenbController {

    private C250Store c250Store;
    private N400Store n400Store;
    private N450Store n450Store;
    private N900Store n900Store;
    private N500Store n500Store;
    private Date nd401 = new Date();
    private String nv401 = (nd401.getMonth() + 1) + "/" + (nd401.getYear() + 1900);
    private int fc250 = 0;
    private int pn850 = 0;
    private int fn450 = 1;
    private String nv402;
    private String nv403;
    private String nv404;
    private String action;
    private int pn400;
    private int daysM;
    private int day = nd401.getDate();
    private int day2 = nd401.getDate();
    private List<N400> lst = new ArrayList<N400>();
    private List<N400> lstN400;
    private int count = 0;
    private Workbook wb = new HSSFWorkbook();
    private Row rowHeader;
    private List<SelectItem> myList = new ArrayList<SelectItem>();

    public OrganizationController() {
        getKapiMenu().setAdmin(false);
        getKapiMenu().setMainmenu1("m20");
        c250Store = new C250Store();
        n400Store = new N400Store();
        n450Store = new N450Store();
        n900Store = new N900Store();
        Object objAction = getRequest().getParameter("action");
        if (objAction != null) {
            action = objAction.toString();
            Object objN450 = getRequest().getParameter("fn450");
            Object objN850 = getRequest().getParameter("pn850");
            Object objDate = getRequest().getParameter("date");
            Object objC250 = getRequest().getParameter("fc250");

            Object objDay = getRequest().getParameter("day");
            Object objN400 = getRequest().getParameter("pn400");
            System.out.println("-------action----" + action);
            if (action.equals("saven400")) {


                if (objN450 != null) {
                    fn450 = Integer.parseInt(objN450.toString());
                    System.out.println("-------fn450----" + fn450);
                }
                if (objN400 != null) {
                    pn400 = Integer.parseInt(objN400.toString());
                    System.out.println("-------pn400----" + pn400);
                }
                System.out.println("-------objDate----" + objDate);
                if (objDate != null) {
                    nv401 = objDate.toString();
                    System.out.println("-------nv401----" + nv401);
                }
                if (objDay != null) {
                    day = Integer.parseInt(objDay.toString());
                    day2 = Integer.parseInt(objDay.toString());
                    System.out.println("-------day----" + day);
                }
                if (objN850 != null) {
                    pn850 = Integer.parseInt(objN850.toString());
                    System.out.println("-------fn850----" + pn850);
                }
                if (objC250 != null && objN850 != null && objN400 != null) {
                    fc250 = Integer.parseInt(objC250.toString());
                    System.out.println("-------fc250----" + fc250);
                    updateN400();
                }
            } else if (action.equals("reload")) {
                if (objN450 != null) {
                    fn450 = Integer.parseInt(objN450.toString());
                    System.out.println("-------fn450----" + fn450);
                }
                if (objDate != null) {
                    nv401 = objDate.toString();
                    System.out.println("-------nv401----" + nv401);
                }
                pn850 = 0;
            } else if (action.equals("empGroup")) {
                if (objN850 != null) {
                    pn850 = Integer.parseInt(objN850.toString());
                    System.out.println("-------pn850----" + pn850);
                }
                if (objDate != null) {
                    nv401 = objDate.toString();
                    System.out.println("-------nv401----" + nv401);
                }
            }
        }





    }

    public List<SelectItem> getListTabC250() {
        try {
            List<C250> lst = c250Store.COMBTABC250(getLogin().getModule(), getLogin().getCompanyNo(), getLogin().getUserName());
            Iterator ic = lst.iterator();
            List<SelectItem> listSelected = new ArrayList<SelectItem>();
            SelectItem item1 = new SelectItem(0, "Vui lòng chọn");
            listSelected.add(item1);
            while (ic.hasNext()) {
                C250 c250 = (C250) ic.next();
                SelectItem item = new SelectItem(c250.getVal(), c250.getLabel());
                listSelected.add(item);
            }
            return listSelected;
        } catch (Exception ex) {
            return null;
        }
    }

    public List<SelectItem> getListTabN450() {
        try {
            List<N450> lst = n450Store.COMBTABN450(getLogin().getModule(), getLogin().getCompanyNo(), getLogin().getUserName());
            Iterator ic = lst.iterator();
            List<SelectItem> listSelected = new ArrayList<SelectItem>();
            while (ic.hasNext()) {
                N450 n450 = (N450) ic.next();
                SelectItem item = new SelectItem(n450.getVal(), n450.getLabel());
                listSelected.add(item);
            }
            System.out.println("size N450" + listSelected.size());
            if (myList.isEmpty()) {
                myList = listSelected;
            }
            return listSelected;
        } catch (Exception ex) {
            System.out.println("loi ne-------:" + ex.getMessage());
            return null;
        }
    }

    public List<SelectItem> getListTime() {
        try {

            List<SelectItem> listSelected = new ArrayList<SelectItem>();
            Calendar c = Calendar.getInstance();

            Date d = new Date();
            d.setMonth(d.getMonth() - 12);
            c.setTime(d);
            for (int i = d.getMonth(); i < d.getMonth() + 18; i++) {
                String m = String.valueOf(c.getTime().getMonth() + 1);
                if (m.length() == 1) {
                    m = "0" + m;
                }
                String s = m + "/ " + (c.getTime().getYear() + 1900);
                String value = m + "/" + (c.getTime().getYear() + 1900);
                System.out.println(s);
                SelectItem item = new SelectItem(value, s);
                listSelected.add(item);
                c.add(Calendar.MONTH, 1);


            }
            return listSelected;
        } catch (Exception ex) {
            System.out.println("loi ne-------:" + ex.getMessage());
            return null;
        }
    }

    public List<N900> getListTabN900R() {
        try {
            System.out.println("N850----N900R----" + pn850);
            List<N900> lst = n900Store.getEmpInGroup(getLogin().getModule(), getLogin().getCompanyNo(), pn850, getLogin().getUserName());

            return lst;
        } catch (Exception ex) {
            System.out.println("loi ne-------:" + ex.getMessage());
            return null;
        }
    }

    public List<N500> getListTabN500M() {
        try {
            java.sql.Date date1 = new java.sql.Date(nd401.getTime());
            System.out.println("---list ---" + getFn450() + "------date:" + getNv401() + "---" + date1);
            if (!nv401.equals("")) {//truong hop set ca theo thang   
                String s = nv401.replace(" ", "");
                String[] date = s.split("/");
                date1 = new java.sql.Date(Integer.parseInt(date[1]) - 1900, Integer.parseInt(date[0]) - 1, day);
            }
            System.out.println("N850----N500M----" + pn850 + "date--:" + date1);
            List<N500> lst = n500Store.LISTOFTABN500M(getLogin().getModule(), getLogin().getCompanyNo(), pn850, 0,
                    0, new java.sql.Date(date1.getTime()),new java.sql.Date(date1.getTime()),0, getLogin().getUserName());

            return lst;
        } catch (Exception ex) {
            System.out.println("loi ne-------:" + ex.getMessage());
            return null;
        }
    }

    public List<N400> getListTabN400() {
        try {

            java.sql.Date date1 = new java.sql.Date(nd401.getTime());
            System.out.println("---list ---" + getFn450() + "------date:" + getNv401() + "---" + date1);
            if (!nv401.equals("")) {//truong hop set ca theo thang   
                String s = nv401.replace(" ", "");
                String[] date = s.split("/");
                date1 = new java.sql.Date(Integer.parseInt(date[1]) - 1900, Integer.parseInt(date[0]) - 1, day);

            } else if (day != date1.getDate()) {
                date1.setDate(day);
            }
            System.out.println("---list ---" + getFn450() + "------date:" + getNv401() + "---" + date1);
            List<N400> lst = n400Store.LISTOFTABN400(getLogin().getModule(), getLogin().getCompanyNo(), new java.sql.Date(date1.getTime()), 0, getFn450(), 0, getLogin().getUserName());
            int stt = 1;
            if (lst.size() > 0) {
                lstN400 = new ArrayList<N400>();
                N400 n400 = lst.get(0);
                lstN400.add(n400);
                pn850 = n400.getPn850();
                fn450 = n400.getFn450();
                if (lst.size() > 1) {
                    for (int i = 1; i < lst.size(); i++) {
                        n400 = lst.get(i);
                        if (n400.getPn850() != lst.get(i - 1).getPn850()) {
                            n400.setDate(stt);
                            lstN400.add(n400);
                            stt++;
                        }
                    }
                }
                System.out.println("Danh sach nhom to size lstN400aaaaaaaaaa----:" + lstN400.size());
            }
            count = 1;

            return lstN400;
        } catch (Exception ex) {
            return null;
        }
    }

    public void updateN400() {
        try {
            System.out.println("-------updateN400");
            java.sql.Date date1 = new java.sql.Date(nd401.getTime());
            if (!nv401.equals("")) {//truong hop set ca theo thang  
                String s = nv401.replace(" ", "");
                String[] date = s.split("/");
//                date1.setDate(day);
//                date1.setMonth(Integer.parseInt(date[0]));
//                date1.setYear(Integer.parseInt(date[1]));
                date1 = new java.sql.Date(Integer.parseInt(date[1]) - 1900, Integer.parseInt(date[0]) - 1, day);
            } else if (day != date1.getDate()) {
                date1.setDate(day);
            }

            //402: ghi chu
            System.out.println("fn400----:" + getPn400() + "fc250----:" + getFc250() + "fn850------:" + getPn850() + "nd401-------:" + date1 + "nv402---------:" + getNv402());
            int roweffect = n400Store.INSERTTABN400(getPn400(), getFc250(), getPn850(), date1, getNv402(), nv403, nv404, getLogin().getUserName());

            System.out.println("-----result ---" + roweffect);

            if (roweffect > 0) {
                showGlobalReport(true, getResourceString("datasavesuccessful"));
            } else {
                showGlobalReport(true, getResourceString("datasuccessfail"));
            }
            sendRedirect("organization.jsp");
        } catch (Exception ex) {
        }
    }

    public List<N400> getCaLamViec() {
        try {
            Calendar calendar = Calendar.getInstance();

            List<N400> lstNum = new ArrayList<N400>();
            System.out.println("countaaaaaaaaaa----:" + count);
            if (count > 0) {
                fn450 = lstN400.get(count - 1).getFn450();
                pn850 = lstN400.get(count - 1).getPn850();
                count++;

                System.out.println("daysM----:" + daysM);
                java.sql.Date date1 = new java.sql.Date(nd401.getTime());

                if (!nv401.equals("")) {//truong hop set ca theo thang   
                    String s = nv401.replace(" ", "");
                    String[] dates = s.split("/");
                    date1 = new java.sql.Date(Integer.parseInt(dates[1]) - 1900, Integer.parseInt(dates[0]) - 1, day);
                    System.out.println("---list ---" + getFn450() + "------date:" + getNv401() + "---" + date1);
                } else if (day != date1.getDate()) {
                    date1.setDate(day);
                }
                calendar.set(date1.getYear(), date1.getMonth(), date1.getDate());
                daysM = calendar.getActualMaximum(Calendar.DAY_OF_MONTH);
                System.out.println("---list ---" + getFn450() + "------date:" + getNv401() + "---" + date1);

                List<N400> lst1 = n400Store.LISTOFTABN400(getLogin().getModule(), getLogin().getCompanyNo(), new java.sql.Date(date1.getTime()), 0, getFn450(), getPn850(), getLogin().getUserName());


                if (lst1.size() > 0) {

                    System.out.println("Danh sach nhom to size----:" + lst1.size());
                    for (int i = 1; i <= daysM; i++) {
                        N400 n400 = new N400();
                        n400.setDate(i);
                        n400.setPn850(pn850);
                        n400.setPn400(0);
                        if (i == date1.getDate()) {
                            n400.setToday(date1.getDate());
                            System.out.println("today = " + date1.getDate());
                        } else {
                            n400.setToday(0);
                        }
                        for (int j = 0; j < lst1.size(); j++) {
                            if (lst1.get(j).getNd401() != null) {
                                if (i == lst1.get(j).getNd401().getDate()) {
                                    n400.setFc250(lst1.get(j).getFc250());
                                    n400.setPn400(lst1.get(j).getPn400());
                                    System.out.println("-------------ngay----" + lst1.get(j).getNd401().getDate());
                                }
                            } else {
                                n400.setFc250(0);
                                n400.setPn400(0);
                            }
                        }

                        lstNum.add(n400);

                    }
                }
            }

            return lstNum;
        } catch (Exception ex) {
            System.out.println("loi ne---" + ex.getMessage());
            return null;
        }
    }

    public List<N400> exportCaLamViec(int identy) {
        try {
            Calendar calendar = Calendar.getInstance();
            int year = nd401.getYear();
            int month = nd401.getMonth();
            int date = nd401.getDay();
            calendar.set(year, month, date);
            daysM = calendar.getActualMaximum(Calendar.DAY_OF_MONTH);
            List<N400> lstNum = new ArrayList<N400>();
            System.out.println("countaaaaaaaaaa----:" + identy);

            fn450 = lstN400.get(identy).getFn450();
            pn850 = lstN400.get(identy).getPn850();

            System.out.println("countaaaaaaaaaa3----:" + identy);
            java.sql.Date date1 = new java.sql.Date(nd401.getTime());

            if (!nv401.equals("")) {//truong hop set ca theo thang   
                String s = nv401.replace(" ", "");
                String[] dates = s.split("/");
                date1 = new java.sql.Date(Integer.parseInt(dates[1]) - 1900, Integer.parseInt(dates[0]) - 1, day);
                System.out.println("---list ---" + getFn450() + "------date:" + getNv401() + "---" + date1);
            } else if (day != date1.getDate()) {
                date1.setDate(day);
            }
            System.out.println("---list ---" + getFn450() + "------date:" + getNv401() + "---" + date1);

            List<N400> lst1 = n400Store.LISTOFTABN400(getLogin().getModule(), getLogin().getCompanyNo(), new java.sql.Date(date1.getTime()), 0, getFn450(), getPn850(), getLogin().getUserName());


            if (lst1.size() > 0) {

                System.out.println("Danh sach nhom to size----:" + lst1.size());
                for (int i = 1; i <= daysM; i++) {
                    N400 n400 = new N400();
                    n400.setDate(i);
                    n400.setPn850(pn850);
                    n400.setPn400(0);
                    if (i == date) {
                        n400.setToday(date);
                    } else {
                        n400.setToday(0);
                    }
                    for (int j = 0; j < lst1.size(); j++) {
                        if (lst1.get(j).getNd401() != null) {
                            if (i == lst1.get(j).getNd401().getDate()) {
                                n400.setFc250(lst1.get(j).getFc250());
                                n400.setCv252(lst1.get(j).getCv252());
                                n400.setPn400(lst1.get(j).getPn400());
                                System.out.println("-------------ngay----" + lst1.get(j).getNd401().getDate());
                            }
                        } else {
                            n400.setFc250(0);
                            n400.setPn400(0);
                        }
                    }

                    lstNum.add(n400);

                }
            }


            return lstNum;
        } catch (Exception ex) {
            System.out.println("loi ne---" + ex.getMessage());
            return null;
        }
    }

    public List<N400> getDayOfMonth() {
        try {
            Calendar calendar = Calendar.getInstance();
            java.sql.Date date1 = new java.sql.Date(nd401.getTime());

            //truong hop set ca theo thang   
            String s = nv401.replace(" ", "");
            String[] dates = s.split("/");
            date1 = new java.sql.Date(Integer.parseInt(dates[1]) - 1900, Integer.parseInt(dates[0]) - 1, day);
            System.out.println("---list ---" + getFn450() + "------date:" + getNv401() + "---" + date1);

            calendar.set(date1.getYear(), date1.getMonth(), date1.getDate());
            daysM = calendar.getActualMaximum(Calendar.DAY_OF_MONTH);
            List<N400> lstNum = new ArrayList<N400>();

            for (int i = 1; i <= daysM; i++) {
                N400 n400 = new N400();
                n400.setDate(i);
                lstNum.add(n400);

            }

            return lstNum;
        } catch (Exception ex) {
            System.out.println("loi ne---" + ex.getMessage());
            return null;
        }

    }

    public List<N400> getDayOfMonth2() {
        try {
            Calendar calendar = Calendar.getInstance();
            int year = nd401.getYear();
            int month = nd401.getMonth();
            int date = nd401.getDay();
            calendar.set(year, month, date);
            daysM = calendar.getActualMaximum(Calendar.DAY_OF_MONTH);
            List<N400> lstNum = new ArrayList<N400>();

            for (int i = 1; i <= 3; i++) {
                N400 n400 = new N400();
                n400.setDate(i);
                ///note
                n400.setPn850(i + 1);
                n400.setPn400(i + 2);
                n400.setFn450(i + 3);
                lstNum.add(n400);

            }


            return lstNum;
        } catch (Exception ex) {
            System.out.println("loi ne---" + ex.getMessage());
            return null;
        }
    }

    /**
     * @return the nd401
     */
    public Date getNd401() {
        return nd401;
    }

    /**
     * @param nd401 the nd401 to set
     */
    public void setNd401(Date nd401) {
        this.nd401 = nd401;
    }

    /**
     * @return the fc250
     */
    public int getFc250() {
        return fc250;
    }

    /**
     * @param fc250 the fc250 to set
     */
    public void setFc250(int fc250) {
        this.fc250 = fc250;
    }

    /**
     * @return the pn850
     */
    public int getPn850() {
        return pn850;
    }

    /**
     * @param pn850 the pn850 to set
     */
    public void setPn850(int pn850) {
        this.pn850 = pn850;
    }

    /**
     * @return the nv402
     */
    public String getNv402() {
        return nv402;
    }

    /**
     * @param nv402 the nv402 to set
     */
    public void setNv402(String nv402) {
        this.nv402 = nv402;
    }

    /**
     * @return the fn450
     */
    public int getFn450() {
        return fn450;
    }

    /**
     * @param fn450 the fn450 to set
     */
    public void setFn450(int fn450) {
        this.fn450 = fn450;
    }

    /**
     * @return the pn400
     */
    public int getPn400() {
        return pn400;
    }

    /**
     * @param pn400 the pn400 to set
     */
    public void setPn400(int pn400) {
        this.pn400 = pn400;
    }

    /**
     * @return the daysM
     */
    public int getDaysM() {
        return daysM;
    }

    /**
     * @param daysM the daysM to set
     */
    public void setDaysM(int daysM) {
        this.daysM = daysM;
    }

    /**
     * @return the nv401
     */
    public String getNv401() {
        return nv401;
    }

    /**
     * @param nv401 the nv401 to set
     */
    public void setNv401(String nv401) {
        this.nv401 = nv401;
    }

    public void exportExcel() throws FileNotFoundException, IOException {
        List<String> header = new ArrayList<String>();

        String s = getRequest().getRequestURL().toString();
        String[] arr = new String[20];
        arr = s.split("/");
        String url = "";
        if (arr.length > 3) {
            url = arr[0] + "/" + arr[1] + "/" + arr[2] + "/kapi/queenb/";
        }
        Sheet sheet = wb.createSheet("N400");

//      FileInputStream fis = null;
//
//      URL urlLogo = new URL("http://upanh.nguoihoian.info/di-PDDN.png");
//        
//      try {
//          File ff = new File(urlLogo.toURI());
//          fis = new FileInputStream(ff);
//      } catch(URISyntaxException e) {
//          fis = new FileInputStream(new File(urlLogo.getPath()));
//      }
//
//        
//      ByteArrayOutputStream img_bytes = new ByteArrayOutputStream();
//      int b;
//      while ((b = fis.read()) != -1) {
//          img_bytes.write(b);
//      }
//      fis.close();
//
//      int ccl = 0;
//      int ccr = 0;
//
//      HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) ccl, ccr, (short) 2, 4);
//      int index = wb.addPicture(img_bytes.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG);
//
//      HSSFPatriarch patriarch = (HSSFPatriarch) sheet.createDrawingPatriarch();
//      patriarch.createPicture(anchor, index);
//      anchor.setAnchorType(2);


        sheet.setColumnWidth(0, 4800);
        for (int i = 0; i < 31; i++) {
            sheet.setColumnWidth(i + 1, 1200);
        }

        HSSFCellStyle stylePlug = (HSSFCellStyle) wb.createCellStyle();
        stylePlug.setFillForegroundColor(HSSFColor.YELLOW.index);
        stylePlug.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        rowHeader = sheet.createRow(0);
        Cell cell3 = rowHeader.createCell(2);
        HSSFCellStyle style3 = (HSSFCellStyle) wb.createCellStyle();
        style3.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        Font fx = wb.createFont();
        fx.setFontHeightInPoints((short) 14);
        style3.setFont(fx);
        cell3.setCellStyle(style3);

        cell3.setCellValue("SaiGon Travel");

        rowHeader = sheet.createRow(1);
        Cell cellxx = rowHeader.createCell(2);
        cellxx.setCellValue("129-131 Luy Bán Bích, P. Tân Thái Hòa , Q. Tân Phú , Tp.HCM");

        Row row_boPhan = sheet.createRow(3);
        Cell cellbp = row_boPhan.createCell(3);
        cellbp.setCellValue("Bộ phận:");
        cellbp = row_boPhan.createCell(5);
        cellbp.setCellStyle(stylePlug);
        String myTemp = "";
        String myValue = Integer.toString(fn450);
        for (int i = 0; i < myList.size(); i++) {
            String strg = myList.get(i).getValue().toString();
            if (strg.equals(myValue)) {
                myTemp = myList.get(i).getLabel();
                break;
            }
        }
        cellbp.setCellValue(myTemp);

        cellbp = row_boPhan.createCell(9);
        cellbp.setCellValue("Thời gian:");
        cellbp = row_boPhan.createCell(11);
        cellbp.setCellValue(nv401);
        cellbp.setCellStyle(stylePlug);

        Cell plug6 = row_boPhan.createCell(6);
        plug6.setCellStyle(stylePlug);
        Cell plug12 = row_boPhan.createCell(12);
        plug12.setCellStyle(stylePlug);

        Row row3 = sheet.createRow(5);

        HSSFCellStyle styleHeader = (HSSFCellStyle) wb.createCellStyle();
        styleHeader.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        styleHeader.setAlignment(CellStyle.ALIGN_CENTER);

        styleHeader.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);
        styleHeader.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        styleHeader.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        styleHeader.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        styleHeader.setBorderRight(HSSFCellStyle.BORDER_THIN);
        styleHeader.setBorderTop(HSSFCellStyle.BORDER_THIN);

        Cell c = row3.createCell(0);
        row3.setHeightInPoints(20);
        c.setCellValue("Tên nhóm tổ");
        c.setCellStyle(styleHeader);

        for (int i = 0; i < 31; i++) {
            Cell cell = row3.createCell(i + 1);
            cell.setCellValue(Integer.toString(i));
            cell.setCellStyle(styleHeader);
        }

        HSSFCellStyle styleBody = (HSSFCellStyle) wb.createCellStyle();
        styleBody.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        styleBody.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        styleBody.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        styleBody.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        styleBody.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        styleBody.setBorderRight(HSSFCellStyle.BORDER_THIN);
        styleBody.setBorderTop(HSSFCellStyle.BORDER_THIN);

        HSSFCellStyle styleBody2 = (HSSFCellStyle) wb.createCellStyle();
        styleBody2.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        styleBody2.setFillForegroundColor(HSSFColor.WHITE.index);
        styleBody2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        styleBody2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        styleBody2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        styleBody2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        styleBody2.setBorderTop(HSSFCellStyle.BORDER_THIN);

        HSSFCellStyle styleBodyLeft = (HSSFCellStyle) wb.createCellStyle();
        styleBodyLeft.setFillForegroundColor(HSSFColor.LIGHT_ORANGE.index);
        styleBodyLeft.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        styleBodyLeft.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        styleBodyLeft.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        styleBodyLeft.setBorderRight(HSSFCellStyle.BORDER_THIN);
        styleBodyLeft.setBorderTop(HSSFCellStyle.BORDER_THIN);
        styleBodyLeft.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        for (int i = 0; i < lstN400.size(); i++) {
            Row row = sheet.createRow(i + 6);
            row.setHeightInPoints(20);
            Cell cell = row.createCell(0);

            cell.setCellValue(lstN400.get(i).getNv852());
            cell.setCellStyle(styleBodyLeft);

            List<N400> lstNum = new ArrayList<N400>();

            lstNum = exportCaLamViec(i);

            for (int j = 0; j < lstNum.size(); j++) {
                Cell c2 = row.createCell(j + 1);
                String temp = "";
                if (lstNum.get(j).getFc250() == 0 && lstNum.get(i).getToday() == 0) {
                    temp = "";
                }
                if (lstNum.get(j).getFc250() == 1 && lstNum.get(i).getToday() == 0) {
                    temp = "A";
                }
                if (lstNum.get(j).getFc250() == 2 && lstNum.get(i).getToday() == 0) {
                    temp = "B";
                }
                if (lstNum.get(j).getFc250() == 3 && lstNum.get(i).getToday() == 0) {
                    temp = "C";
                }
                if (lstNum.get(j).getFc250() == 0 && lstNum.get(i).getToday() != 0) {
                    temp = "";
                }
                if (lstNum.get(j).getFc250() == 1 && lstNum.get(i).getToday() != 0) {
                    temp = "A";
                }
                if (lstNum.get(j).getFc250() == 2 && lstNum.get(i).getToday() != 0) {
                    temp = "B";
                }
                if (lstNum.get(j).getFc250() == 3 && lstNum.get(i).getToday() != 0) {
                    temp = "C";
                }

                c2.setCellValue(temp);
                if (i % 2 == 0) {
                    c2.setCellStyle(styleBody);
                } else {
                    c2.setCellStyle(styleBody2);
                }
            }
        }

//        String tempStr = ResourcesProvider.getReportExcel();
//        if (!(new File(tempStr)).exists()) {
//            (new File(tempStr)).mkdirs();
//        }

        File fileout = new File(ResourcesProvider.getReportExcel() + "report.xls");

        FileOutputStream fileOut = new FileOutputStream(fileout);
        wb.write(fileOut);
        fileOut.close();

        sendRedirect(url + "resources/excel/report.xls");
        //sendRedirectExcel(ResourcesProvider.getReportExcel() + "report.xls");
    }
}
